import os
import numpy as np
import pandas as pd
from pyecharts import Bar
import sqlite3
from pyecharts import Pie

if __name__=='__main__':
    db_name='../db.sqlite3'

else:
    db_name='db.sqlite3'

class ChipotleNan:
    def __init__(self):
        self.conn=sqlite3.connect(db_name)

        self.df=pd.read_sql('SELECT * FROM chipotle_csv',self.conn)

#生成图
    def bar(self,axis0,axis1,name='test',table_name='test1'):
        bar=Bar(name)
        bar.add(table_name, axis0, axis1, is_label_show=True, is_datazoom_show=True)
        return bar


    def series_to_pie(self,series,name='test',table_name='test1'):
        pie= Pie(name)
        axis0=series.index
        axis1=np.round(series.values.ravel(),1)

        pie.add(table_name,axis0,axis1,is_label_show=False,
                label_text_color=None,#标签颜色
                legend_orient='vertical',#图例垂直
                 legend_pos='left')
        return pie


    def series_to_bar(self,series,name='test',table_name='test1'):
        bar=Bar(name)
        axis0=series.index
        axis1=np.round(series.values.ravel(),1)
        bar.add(table_name,axis0,axis1,is_lable_show=True,is_datazoom_show=True)
        return bar






#题
    def one_amount(self):
        amount=self.df.quantity*(self.df.item_price.str.replace('$','').astype('float64'))
        amount=amount.sum()
        return amount

    def two_topic(self):
        df1=self.df.copy()
        df1_period=df1.groupby('item_name').sum()
        bar=df1_period.sort_values(['quantity'],ascending=False)
        return bar.head(1)


    def three_topic(self):
        amount=self.df.auantity*(self.df.item_price.str.replace('$','').astype('float64'))
        df1=self.df.copy()
        df1['amount']=amount
        a=df1.sort_value(['amount'],ascending=False)
        return  a.head(1)


    def four_topic(self):
        df1=self.df.groupby('item_name').sum()
        df1.drop(['order_id','index'],axis=1,inplace=True)
        return df1

    def bar_how_many_are_sold_per_item(self):
        info=self.four_topic()
        bar=self.series_to_bar(info)
        return bar


    def five_topic(self):
        ipr=self.df.quantity*(self.df.item_price.str.replace('$','').astype('float64'))
        df1 = self.df.copy()
        df1['ipr'] = ipr
        df1_grounp = df1.groupby('item_name').sum()
        df1_grounp = df1_grounp.drop('order_id', axis=1, inplace=True)
        df1_grounp = df1_grounp.drop('quantity', axis=1, inplace=True)
        va = df1_grounp.values  # 生成一个
        print(va.shape)
        return df1_grounp

    def bar_how_much_are_sold_per_item(self):
        info = self.four_topic()
        bar = self.series_to_bar(info, '每种商品销售金额')
        return bar

    def six_topic(self):
        df1 = self.df.copy()
        df1_group = df1.drop_duplicates(['item_name', 'choice_description'])
        df1_group = df1_group.drop('order_id', axis=1)
        df1_group = df1_group.drop('quantity', axis=1)

        return df1_group

    def pie_proportion_of_the_amount_of_goods(self):
        attr = self.df.item_name
        attr1 = attr.head(10)
        a = self.df.quantity.astype('float64')
        b = self.df.item_price.str.replace('$', '').astype('float64')
        week_sum = (a * b)
        week1_new = int(list(week_sum.cumsum())[9])
        b = b.head(10).values
        percent = b / week1_new

        pie = Pie('每种商品的销售金额')
        pie.add('商品销售', attr1, percent, is_label_show=True)
        return pie


    def seven_topic(self):
        df1=self.df.copy()
        df1 = df1.item_price.str.replace("$", " ").astype('float64')
        self.df['new_price'] = df1
        df2 = self.df
        df3 = df2.groupby('item_name').sum().sort_values(by='new_price',ascending=False)
        df3.drop(['index','order_id','quantity'],axis=1,inplace=True)
        df3=df3.head(10)
        print(df3.values)
        # df4 = df3.drop(['order_id', 'quantity'], axis=1)

        pie=Pie('每种商品销售金额在流水总额中所占的比例')
        pie.add('所占比例额',df3.index,df3.values,is_label_show = False,       #是否显示标签
                        label_text_color = None,    #标签颜色
                        legend_orient = 'vertical', #图例垂直
                        legend_pos = 'right')
        return  pie



    def eight_topic(self):
        df1 = self.df.item_price.str.replace("$", " ").astype('float64')

        self.df['new_price'] = df1

        df2 = self.df

        df3 = df2.groupby('order_id').sum()

        df4 = df3.drop('quantity', axis=1)
        df4=df4.drop('index',axis=1)


        bar=self.series_to_bar(df4.head(200),'每个订单金额',table_name='订单id')
        return bar




    def nine_topic(self):
        df1 = self.df.copy()
        product = df1[(df1['item_name'] == 'Chicken Bowl')]
        most = product.groupby(by='choice_description').sum().sort_values('quantity', ascending=False)
        return most


    def ten_topic(self):
        df1 = self.df.copy()
        aa=df1.item_name=='Chicken Bowl'
        df2=df1[aa]
        df3=df2.groupby('choice_description').count()['index']
        print(df3)
        pie = Pie('Chicken Bowl”每种配料选择的次数，及所占比例')
        pie.add('占比', df3.index, df3.values, is_lable_show=True)
        return pie


if __name__=='__main__':
    x=ChipotleNan()
    x.ten_topic()